package com.loongtech.core.util;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

public class ExcelUtils {

	/**
	 * 
	 * @param workbook
	 * @return
	 */
	public static Map<String, CellStyle> getCellStyles(Workbook workbook) {
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		DataFormat df = workbook.createDataFormat();

		// header style
		CellStyle style;
		Font headerFont = workbook.createFont();
		headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = createBorderedStyle(workbook);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setFont(headerFont);
		styles.put("header", style);

		// normal data
		style = createBorderedStyle(workbook);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(true);
		styles.put("normal", style);

		// error data
		Font errorFont = workbook.createFont();
		errorFont.setColor(IndexedColors.RED.getIndex());
		style = createBorderedStyle(workbook);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(true);
		style.setFont(errorFont);
		styles.put("error", style);

		// green data
		Font greenFont = workbook.createFont();
		greenFont.setColor(IndexedColors.GREEN.getIndex());
		style = createBorderedStyle(workbook);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(true);
		style.setFont(greenFont);
		styles.put("green", style);

		// date style
		style = createBorderedStyle(workbook);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(true);
		style.setDataFormat(df.getFormat("yyyy-MM-dd"));
		styles.put("date", style);

		// date style
		style = createBorderedStyle(workbook);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(true);
		style.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm"));
		styles.put("date_2", style);

		// error date style
		style = createBorderedStyle(workbook);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(true);
		style.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm"));
		style.setFont(errorFont);
		styles.put("error_date_2", style);

		// number
		style = createBorderedStyle(workbook);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setWrapText(true);
		style.setDataFormat(df.getFormat("###,###0"));
		styles.put("number", style);

		// number
		style = createBorderedStyle(workbook);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setWrapText(true);
		style.setDataFormat(df.getFormat("###,###0.0"));
		styles.put("number_2", style);

		return styles;
	}

	public static void createCell(Row row, int column, CellStyle style, int cellType, Object value) {
		Cell cell = row.createCell((short) column);
		if (style != null) {
			cell.setCellStyle(style);
		}
		switch (cellType) {
		case Cell.CELL_TYPE_BLANK:
			break;

		case Cell.CELL_TYPE_STRING:
			if (value != null) {
				cell.setCellValue(value.toString());
			}
			break;

		case Cell.CELL_TYPE_NUMERIC:
			cell.setCellType(cellType);
			if (value != null) {
				cell.setCellValue(Double.parseDouble(value.toString()));
			}
			break;

		default:
			break;
		}
	}

	private static CellStyle createBorderedStyle(Workbook wb) {
		CellStyle style = wb.createCellStyle();
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		return style;
	}

	public static Workbook getExcelWorkbook(List<List<String>> datas, List<String> columnnames) throws Exception {
		Workbook workbook = null;
		workbook = new HSSFWorkbook();
		Map<String, CellStyle> wbStyles = getCellStyles(workbook);
		if (datas != null && datas.size() > 0) {
			int perNum = 30000;
			int len = datas.size();
			if (len > 100000) {
				len = 100000;
			}
			int sheetNum = len / perNum + 1;
			for (int i = 0; i < sheetNum; i++) {
				int startNo = perNum * i;
				int endNo = perNum * (i + 1);
				if (endNo > len) {
					endNo = len;
				}
				generalSheet(workbook, datas, columnnames, wbStyles, "sheet" + i, startNo, endNo);
			}

		} else {
			generalSheet(workbook, datas, columnnames, wbStyles, "sheet", 0, 0);
		}
		return workbook;
	}

	public static Workbook getExcelWorkbooka(List<List<List<String>>> ldatas, List<String> sheetNames,
			List<List<String>> columnnames) {
		Workbook workbook = null;
		try {
			workbook = new HSSFWorkbook();
			Map<String, CellStyle> wbStyles = getCellStyles(workbook);
			if (ldatas.size() > 0)
				generalSheet(workbook, wbStyles, sheetNames, columnnames, ldatas);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return workbook;
	}

	private static void generalSheet(Workbook workbook, Map<String, CellStyle> wbStyles, List<String> sheetNames,
			List<List<String>> columnnames, List<List<List<String>>> ldatas) {
		int i = 0;

		for (String sheetName : sheetNames) {
			Sheet sheet = workbook.createSheet(sheetName);
			// 设置列宽
			int col = 0;
			if (columnnames != null) {
				for (String columnname : columnnames.get(i)) {
					sheet.setColumnWidth((short) col++, 5000);
				}
			}
			// 创建第一行标题
			Row row = sheet.createRow((short) 0);
			col = 0;
			for (String columnname : columnnames.get(i)) {
				ExcelUtils
						.createCell(row, (short) col++, wbStyles.get("header"), XSSFCell.CELL_TYPE_STRING, columnname);
			}
			int lineNum = 1;
			for (int j = 0; j < ldatas.get(i).size(); j++) {
				List<String> rowDatas = ldatas.get(i).get(j);
				Row rowl = sheet.createRow((short) (lineNum++));
				col = 0;
				for (String data : rowDatas) {
					try {
						Integer.valueOf(data);
						ExcelUtils.createCell(rowl, (short) col++, wbStyles.get("normal"), XSSFCell.CELL_TYPE_NUMERIC,
								data);
					} catch (Exception e) {
						ExcelUtils.createCell(rowl, (short) col++, wbStyles.get("normal"), XSSFCell.CELL_TYPE_STRING,
								data);
					}
				}
			}
			i++;
		}

	}

	private static void generalSheet(Workbook workbook, List<List<String>> datas, List<String> columnnames,
			Map<String, CellStyle> wbStyles, String sheetName, int start, int end) {
		Sheet sheet = workbook.createSheet(sheetName);

		// 设置列宽
		int col = 0;
		if (columnnames != null) {
			for (String columnname : columnnames) {
				sheet.setColumnWidth((short) col++, 5000);
			}
		}

		if (end > start) {
			// 创建第一行标题
			Row row = sheet.createRow((short) 0);
			col = 0;
			for (String columnname : columnnames) {
				createCell(row, (short) col++, wbStyles.get("header"), XSSFCell.CELL_TYPE_STRING, columnname);
			}
			int lineNum = 1;
			for (int i = start; i < end; i++) {
				List<String> rowDatas = datas.get(i);
				Row rowl = sheet.createRow((short) (lineNum++));
				col = 0;
				for (String data : rowDatas) {
					createCell(rowl, (short) col++, wbStyles.get("normal"), XSSFCell.CELL_TYPE_STRING, data);
				}
			}
		}
	}
}
